package persistencia;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.Vector;
import persistencia.Broker;
import persistencia.Conexion;
import dominio.Invitacion;
import dominio.Revisor;

import dominio.Revision;

public class RevisionDAO {
	
	public static Revision newRevision(int articulo, String revisor) throws Exception {
		Revision result=null;
		String SQL="Select * from RevisionArticulo where articulo=? and revisor=?";
		Conexion bd=null;
		Exception ex=null;
		PreparedStatement p=null;
		try {
			bd=Broker.get().getBD();
			p=bd.prepareStatement(SQL);
			p.setInt(1, articulo);
			p.setString(2, revisor);
			ResultSet r=p.executeQuery();
			if (r.next()) {
				result=new Revision();				
				result.setRevisor(RevisorDAO.newRevisor(r.getString("revisor")));
				result.setEstado(r.getString("estado"));
				result.setArticulo(ArticuloDAO.newArticulo(r.getInt("articulo")));
				result.setFechaPeticion(r.getString("fechaPeticion"));
				result.setFechaFinalizacion(r.getString("fechaFinalizacion"));
				result.setComentariosRevisor(r.getString("comentariosRevisor"));
				result.setComentariosEditor(r.getString("comentariosEditor"));
				
			} else {
				throw new Exception("La recomendaci�n del revisor " +revisor+ " para el articulo "+articulo+" no existe");
			}
		}
		catch (Exception e) {
			ex=e;
		}
		finally {
			bd.close();
			if (ex!=null)
				throw ex;
		}
		return result;
	}
	
		
	public static void insert(Revision revision) throws Exception {
		String SQL="Insert into RevisionArticulo values (?,?,?,?,?,?,?);";
		Conexion bd=null;
		Exception ex=null;
		PreparedStatement p=null;
		try {
			bd=Broker.get().getBD();
			p=bd.prepareStatement(SQL);
			p.setString(1, revision.getRevisor().getDni());
			p.setInt(2, revision.getArticulo().getIdArticulo());
			p.setString(3, revision.getEstado());
			p.setString(4, revision.getFechaPeticion());
			p.setString(5, revision.getFechaFinalizacion());
			p.setString(6, revision.getComentariosRevisor());
			p.setString(7, revision.getComentariosEditor());
			//p.executeQuery();
			p.executeUpdate();
		}
		catch (Exception e) {
			ex=e;
		}
		finally {
			bd.close();
			if (ex!=null)
				throw ex;
		}
	}

	public static void delete(Revision revision) throws Exception {
		String SQL="Delete from RevisionArticulo where articulo=? and revisor=?";
		Conexion bd=null;
		Exception ex=null;
		PreparedStatement p=null;
		try {
			bd=Broker.get().getBD();
			p=bd.prepareStatement(SQL);
			p.setInt(1, revision.getArticulo().getIdArticulo());
			p.setString(2, revision.getRevisor().getDni());
			p.executeUpdate();
		}
		catch (Exception e) {
			ex=e;
		}
		finally {
			bd.close();
			if (ex!=null)
				throw ex;
		}	
	}
	
	public static void actualizar(Revision revision) throws Exception {
		String SQL="Update RevisionArticulo set estado=?, fechaPeticion=?, fechaFinalizacion=?, comentariosRevisor=?, comentariosEditor=?" +
				"where articulo=? and revisor=?;";
		Conexion bd=null;
		Exception ex=null;
		PreparedStatement p=null;
		try {
			bd=Broker.get().getBD();
			p=bd.prepareStatement(SQL);
			p.setString(7, revision.getRevisor().getDni());
			p.setInt(6, revision.getArticulo().getIdArticulo());
			p.setString(1, revision.getEstado());
			p.setString(2, revision.getFechaPeticion());
			p.setString(3, revision.getFechaFinalizacion());
			p.setString(4, revision.getComentariosRevisor());
			p.setString(5, revision.getComentariosEditor());
			p.executeUpdate();
		}
		catch (Exception e) {
			ex=e;
		}
		finally {
			bd.close();
			if (ex!=null)
				throw ex;
		}
	}
	
	//Devuelve las revisiones de un articulo
	public static Vector<Revision> RevisionArticulo(int idArt) throws Exception {
        
		Vector<Revision> result=new Vector<Revision>();

		String SQL;
		SQL="Select * from RevisionArticulo where articulo=?;";
		
		Conexion bd=null;
		Exception ex=null;
		PreparedStatement p=null;
		try {
			bd=Broker.get().getBD();
			p=bd.prepareStatement(SQL);
			p.setInt(1,idArt);
			ResultSet r=p.executeQuery();
			while (r.next()) {
				result.add(newRevision(idArt,r.getString("revisor")));
			}			
		}
		catch (Exception e) {
			ex=e;
		}
		finally {
			bd.close();
			if (ex!=null)
				throw ex;
		}
		return result;
	}
	
	//Devuelve las revisiones de un articulo
		public static Vector<Revision> RevisionArticuloIniciada(int idArt) throws Exception {
	        
			Vector<Revision> result=new Vector<Revision>();

			String SQL;
			SQL="Select * from RevisionArticulo where articulo=? and estado='INICIADA';";
			
			Conexion bd=null;
			Exception ex=null;
			PreparedStatement p=null;
			try {
				bd=Broker.get().getBD();
				p=bd.prepareStatement(SQL);
				p.setInt(1,idArt);
				ResultSet r=p.executeQuery();
				while (r.next()) {
					result.add(newRevision(idArt,r.getString("revisor")));
				}			
			}
			catch (Exception e) {
				ex=e;
			}
			finally {
				bd.close();
				if (ex!=null)
					throw ex;
			}
			return result;
		}
	
	//Devuelve las revisiones de un revisor
	public static Vector<Revision> RevisionesArticulo(String rev) throws Exception {
        
		Vector<Revision> result=new Vector<Revision>();

		String SQL;
		SQL="Select * from RevisionArticulo where revisor=? and estado='INICIADA';";
		
		Conexion bd=null;
		Exception ex=null;
		PreparedStatement p=null;
		try {
			bd=Broker.get().getBD();
			p=bd.prepareStatement(SQL);
			p.setString(1,rev);
			ResultSet r=p.executeQuery();
			while (r.next()) {
				result.add(newRevision(r.getInt("articulo"),rev));
			}			
		}
		catch (Exception e) {
			ex=e;
		}
		finally {
			bd.close();
			if (ex!=null)
				throw ex;
		}
		return result;
	}
	
	public static void updateComentariosRevisor(String revisor, String contenido, int idArticulo) throws Exception {
		String SQL="UPDATE RevisionArticulo SET comentariosRevisor=? where revisor= ? and articulo= ?;";
		Conexion bd=null;
		Exception ex=null;
		PreparedStatement p=null;
		try {
			bd=Broker.get().getBD();
			p=bd.prepareStatement(SQL);
			p.setString(1, contenido);
			p.setString(2, revisor);
			p.setInt(3, idArticulo);
			p.executeUpdate();		
		}
		catch (Exception e) {
			ex=e;
		}
		finally {
			bd.close();
			if (ex!=null)
				throw ex;
		}
	}
	
	public static void updateEstado(String estado, String revisor, String contenido, int idArticulo) throws Exception {
		Calendar today = new GregorianCalendar(); 
        SimpleDateFormat df = new SimpleDateFormat();
        df.applyPattern("dd/MM/yyyy");
		
		String SQL="UPDATE RevisionArticulo SET estado=?, fechaFinalizacion=?, comentariosRevisor=? where revisor= ? and articulo= ?;";
		Conexion bd=null;
		Exception ex=null;
		PreparedStatement p=null;
		try {
			bd=Broker.get().getBD();
			p=bd.prepareStatement(SQL);
			p.setString(1, estado);
			p.setString(2, df.format(today.getTime()));
			p.setString(3, contenido);
			p.setString(4, revisor);
			p.setInt(5, idArticulo);
			p.executeUpdate();					
		}
		catch (Exception e) {
			ex=e;
		}
		finally {
			bd.close();
			if (ex!=null)
				throw ex;
		}
	}
	
	
	
}